﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using EfDbLayer;

namespace DbClient
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new DbEntities(GetConnectionString()))
            {
                foreach (var article in db.Articles)
                {
                    Console.WriteLine(article.ArticleTitle);
                }
            }

            var res = GetAllWordsData(1, 200, null);
        }

        private static void TestProc()
        {
            using (var db = new DbEntities(GetConnectionString()))
            {
                string queryString = "SELECT Words.WordId, Words.WordText, SUM(ArticleWords.WordCount) AS WordCountSum" +
                            " FROM Words INNER JOIN " +
                            " ArticleWords ON Words.WordId = ArticleWords.WordId " +
                            " GROUP BY Words.WordId, Words.WordText " +
                            " ORDER BY WordCountSum OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY";

                db.Connection.Open();

                var cmd = (db.Connection as EntityConnection).StoreConnection.CreateCommand();

                //var cmd = db.Connection.CreateCommand();
                cmd.CommandText = queryString;
                cmd.CommandType = CommandType.Text;

                var res = cmd.ExecuteReader();

                while (res.Read())
                {
                    Console.WriteLine(String.Format("{0}, {1}, {2}", res[0], res[1], res[2]));
                }
            }
        }

        public static Dictionary<string, int> GetAllWordsData(int page, int itemOnPages, string source)
        {
            var dic = new Dictionary<string, int>();

            using (var db = new DbEntities(GetConnectionString()))
            {
                var toSkip = page * itemOnPages;

                string queryString = "SELECT Words.WordText, SUM(ArticleWords.WordCount) AS WordCountSum" +
                            " FROM Words INNER JOIN " +
                            " ArticleWords ON Words.WordId = ArticleWords.WordId " +
                            " GROUP BY Words.WordText " +
                            " ORDER BY WordCountSum OFFSET @toOffset ROWS FETCH NEXT @toTake ROWS ONLY";

                db.Connection.Open();

                var cmd = (db.Connection as EntityConnection).StoreConnection.CreateCommand();

                //var cmd = db.Connection.CreateCommand();
                cmd.CommandText = queryString;

                {
                    var par = cmd.CreateParameter();
                    par.ParameterName = "toOffset";
                    par.Value = toSkip;
                    cmd.Parameters.Add(par);
                }

                {
                    var par = cmd.CreateParameter();
                    par.ParameterName = "toTake";
                    par.Value = itemOnPages;
                    cmd.Parameters.Add(par);
                }

                cmd.CommandType = CommandType.Text;
                //cmd.Parameters.Add();

                var res = cmd.ExecuteReader();

                while (res.Read())
                {
                    var num = res[1] as int?;
                    int count = 0;
                    if (num.HasValue)
                        count = num.Value;
                    dic.Add(res[0].ToString(), count);
                }
            }

            return dic;
        }

        private class TmpClass
        {
            public string Text { get; set; }

            public int Count { get; set; }
        }

        private static string GetConnectionString()
        {
            var entityBuilder = new EntityConnectionStringBuilder
                                    {
                                        Provider = "System.Data.SqlServerCe.4.0",
                                        ProviderConnectionString = CN,
                                        Metadata = @"res://*/EfDbModel.csdl|res://*/EfDbModel.ssdl|res://*/EfDbModel.msl",
                                    };

            Console.WriteLine(entityBuilder.ToString());

            return entityBuilder.ToString();
        }

        private static string GetProviderString()
        {
            var sqlBuilder = new SqlConnectionStringBuilder { DataSource = CN };


            return sqlBuilder.ToString();
        }

        private const string CN = @"Data Source=D:\zagir\Development\wordstat\WordStat\test.sdf";
        private const string FILE_NAME = @"D:\zagir\Development\wordstat\WordStat\test.sdf";
    }

    public class QueryRes
    {
        public int WordId { get; set; }
        public string WordText { get; set; }
        public int WordCountSum { get; set; }
    }
}
